﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;


public class clsConnection
{
    const string STR_CONN = "Data Source=FAN-PC\\SQLEXPRESS;Initial Catalog=MAMNONHOAHONG;Integrated Security=True";
    protected SqlConnection sqlconn;
    SqlCommand sqlcomm = new SqlCommand();
	public clsConnection()
	{
	}

    //Ket noi csdl
    public Boolean connectDatabase()
    {
        sqlconn = new SqlConnection();
        try
        {
            sqlconn.ConnectionString = STR_CONN;
            if (sqlconn.State != ConnectionState.Closed)
            {
                sqlconn.Close();
            }
            sqlconn.Open();
            
            return true;
        }
        catch
        {

            return false;
        }
        
    }
    protected void Dispose()
    {
        try
        {
            sqlconn.Close();
            sqlconn.Dispose();
            sqlcomm.Dispose();
            sqlcomm.Parameters.Clear();
        }
        catch
        {
        }
    }

    public void AddParameter(SqlParameter para)
    {
         sqlcomm.Parameters.Add(para);
    }

    //Thuc thi lenh select
    protected DataTable ExcuteQuery(string sql)
    {
        DataTable dt = new DataTable();

        try
        {
            if (connectDatabase())
            {
                SqlDataAdapter da = new SqlDataAdapter(sql, sqlconn);
                da.Fill(dt);
                
            }
        }
        catch
        {
        }
        finally
        {
            this.Dispose();
        }
        return dt;
    }

    //Thuc thi lenh them sua xoa
    protected Boolean ExcuteNonQuery(string sql)
    {
        try
        {
            if (connectDatabase())
            {
                sqlcomm.CommandText = sql;
                sqlcomm.CommandType = CommandType.Text;
                sqlcomm.Connection = sqlconn;
                sqlcomm.ExecuteNonQuery();
            }
            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            this.Dispose();
        }
    }
    public Boolean runSQL(String sql)
    {

        try
        {
            if (!connectDatabase()) return false;
            SqlCommand sqlcomm = new SqlCommand();
            sqlcomm.CommandType = CommandType.Text;
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandText = sql;
            sqlcomm.ExecuteNonQuery();
            Dispose();
            return true;
        }
        catch
        {
            return false;
        }
    }

    protected int ExecuteScalar(string sql)
    {
        int temp = 0;
        try
        {
            if (connectDatabase())
            {
                sqlcomm.CommandText = sql;
                sqlcomm.CommandType = CommandType.Text;
                sqlcomm.Connection = sqlconn;
                temp = int.Parse(sqlcomm.ExecuteScalar().ToString());
                
            }
        }
        catch
        {
        }
        finally
        {
            this.Dispose();
        }
        return temp;
    }
}